import pymysql


class DatabaseConnector:
    def __init__(self):
        self.connect = pymysql.connect(host='localhost',
                                       user='root',
                                       password='root',
                                       charset='utf8',
                                       database='comics')
        self.cursor = self.connect.cursor()

    # 创建漫画信息表
    def create_table_comics_info(self):
        sql = """
            create table comics_info(comics_id bigint primary key auto_increment,
            comics_name varchar(255),
            comics_label varchar(100),
            category varchar(100),
            author varchar(255), 
            reduce varchar(255),
            cover_path  varchar(100),
            comics_url varchar(50),
            cover_url varchar(255)
            )
        """
        try:
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as e:
            print(e)
        print("创建漫画信息表成功")

    # 创建漫画对应的章节
    def create_table_comics_chapter(self):
        sql = """
            create table comics_chapter(
                id bigint primary key auto_increment,
                comics_id bigint,
                chapter_name varchar(100),
                chapter_url varchar(100),
                chapter_dir varchar(100),
                constraint  chapter_comics_info foreign key(comics_id) references  comics_info(comics_id)
            )
        """
        try:
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as e:
            print(e)
        print("创建漫画章节表成功")

    # 添加漫画信息
    def insert_comics_info(self, comics_name: str, comics_label: str, category: str, author: str, reduce: str,
                           cover_path: str, comics_url: str,
                           cover_url: str):
        sql = "insert into comics_info(comics_name, comics_label, category, author, reduce, cover_path, comics_url, cover_url) values('{}','{}','{}','{}','{}','{}','{}','{}')".format(
            comics_name, comics_label, category, author, reduce, cover_path, comics_url, cover_url)
        try:
            result = self.cursor.execute(sql)
            if result:
                self.connect.commit()
                print("添加{}成功".format(comics_name))
        except Exception as e:
            print(e)
        self.close()

    # 获取所有的信息
    def get_all_comics(self):
        comics_list = []
        sql = "select * from comics_info"
        try:
            result = self.cursor.execute(sql)
            if result:
                result = self.cursor.fetchall()

                for items in result:
                    one_comics_list = []
                    for value in items:
                        one_comics_list.append(value)
                    comics_list.append(one_comics_list)
                return comics_list
        except Exception as e:
            print(e)
        self.close()
        return None

    #  返回漫画对应的id与漫画url
    def get_comics_id_url(self):
        sql = "select comics_id, comics_url from comics_info"
        try:
            result = self.cursor.execute(sql)
            if result:
                result = self.cursor.fetchall()
                return result
        except Exception as e:
            print(e)
        self.close()
        return None

    # 添加漫画章节
    def insert_comics_chapter(self, comics_id: int, chapter_name: str, chapter_url: str, chapter_dir: str):
        sql = "insert into comics_chapter(comics_id, chapter_name, chapter_url, chapter_dir) values ({}, '{}', '{}', '{}')".format(
            comics_id, chapter_name, chapter_url, chapter_dir)
        try:
            result = self.cursor.execute(sql)
            if result:
                self.connect.commit()
                print("添加{}成功".format(chapter_name))
                return True
        except Exception as e:
            print(e)
        self.close()
        return False

    # 通过蛮荒id获取漫画章节信息
    def get_comics_chapter(self, comics_id: int):
        sql = "select chapter_name, chapter_dir from comics_chapter where comics_id={}".format(comics_id)
        try:
            result = self.cursor.execute(sql)
            if result:
                result = self.cursor.fetchall()
                return result
        except Exception as e:
            print(e)
        self.close()
        return ()

    def close(self):
        self.connect.close()


if __name__ == "__main__":
    dc = DatabaseConnector()
    # dc.get_comics_chapter(1)
    dc.create_table_comics_info()
    dc.create_table_comics_chapter()
